1. Field of the Invention
The present invention relates to computer based calculations and more specifically those performed by programs commonly known as “spreadsheets”. Spreadsheets provide a system for arranging sets of calculations and data within an organized layout. Spreadsheets are comprised of a series of horizontal and vertically aligned adjacent regions referred to as cells. Within these cells a user can enter formulas consisting of a series of operators and operands in infix notation. Once the formula is entered into a cell the spreadsheet program can be set to perform the calculation and display the results within the cell containing the formula. The formulas can also be built with interdependency in which one formula references the contents of a separate cell which can contain data or another formula. When the referenced cell contains another formula the results from that formula are used as a component of the calculation being preformed by the referring formula.
Spreadsheet programs provide an efficient tool for performing calculations. However, within such a system the user has no direct means of knowing whether referenced cells contain constant values—and if so what they might be—or if they are the result of further calculations taking place elsewhere from within a separate formula contained in the referenced cell. The problem is compounded by the fact the formulas are often not clearly viewed all at once; since they can be spread throughout cell locations and even found in entirely separate screen views and files. The present invention proposes a means of assisting the user with managing spreadsheets by providing a graphical representation of the calculations contained in the cells.
2. Description of the Related Art
There are existing processes which define how to create a graphical representation of spreadsheet formulas such as Spencer at el. (U.S. Pat. No. 5,603,021 issued Feb. 11, 1997). Spencer's methods translate spreadsheet formula notation into graphical hierarchies to assist in data entry. Within Spencer's process a referenced cell contained in a formula is displayed as a reference node within the formulas graphical representation. This provides a simple view of a formula that is useful to assist in data entry. However, the present invention takes a novel approach by creating new compound expressions which appends the expression hierarchy of a referenced cell as a descendant within the expression hierarchy of the original formula containing the reference. This process is carried out for each newly appended descendant expression until no further references are present. Such a process creates complex structures that display the complete flow of calculation across separately located formulas. This provides a new way to view the underlying behavior between formulas that governs the final results displayed in the spreadsheet.
There are also existing processes used to compare formulas for similarities so they can be associated in groups used to support such features as auto completion during data entry and locating inconsistent formulas within a contiguous range of cells. However, the present invention employs the process of building compound expressions. When the methods of the prior art compare formulas for equivalency they are concerned with the immediate structure of the formula within an individual cell and group based on that structure. However, the present invention is combining the expressions of a referring formula together with the expressions of any referenced formulas and comparing these compound expressions for equivalency. These two distinct approaches will produce entirely separate results. For example in FIG. 30 the formulas in cells A1, B1, and C1 all share a similar structure and would be considered equivalent by the prior art. However, since the present invention is appending the expression hierarchies representing the contents of the referenced cells the newly formed compound expressions are no longer equivalent and would not be grouped as such.
Since compound expressions are built from a hierarchy of two or more separate referenced expressions, each descendant expression contained within in a compound expression is also compared for similarity. Within a compound expression a distinct instance of each referenced expression is appended as a descendant expression of each node that references its containing formula's cell to form the full compound expression hierarchy. Therefore, in the context of a compound expression a distinct grouping of the descendant expressions exists for each set of similar descendant expressions that are referenced by a common reference node or a successive set of descending reference nodes within the compound expression hierarchy. The building of such shared expression groups within a compound expression is further illustrated in FIGS. 13 through 30. These expression groups can not be realized through the methods of the prior art and require the exhaustive reference linking of the compound expression building process provided by the present invention.